﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class DxcDAL:IDxcDAL
    {
        public int Add(DxcInfo dx)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, dx.Num),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, dx.Ku),
                DALHelper.MakeInParam(":jday", OracleType.VarChar, dx.Jday),
                DALHelper.MakeInParam(":jbanzu", OracleType.VarChar, dx.Jbanzu),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, dx.Neirong),
                DALHelper.MakeInParam(":cs", OracleType.Number, dx.Cs),
                DALHelper.MakeInParam(":dun", OracleType.Number, dx.Dun)
            };
            return (DALHelper.RunCommand(@"insert into dd.DXC (num, ku, jday, jbanzu, neirong, cs, dun) values(:num, :ku, to_date(:jday,'yyyy-mm-dd hh24:mi:ss'), :jbanzu, :neirong, :cs, :dun)", prams));
        }

        public int Update(string num, int cs, decimal dun)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":cs", OracleType.Number, cs),
                DALHelper.MakeInParam(":dun", OracleType.Number, dun)
            };
            return (DALHelper.RunCommand(@"update dd.DXC set cs = :cs, dun = :dun where num = :num", prams));
        }
        public int UpdateCL(string num, string clqk, string clday, string cbanzu)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":clqk", OracleType.VarChar, clqk),
                DALHelper.MakeInParam(":clday", OracleType.VarChar, clday),
                DALHelper.MakeInParam(":cbanzu", OracleType.VarChar, cbanzu)
            };
            return (DALHelper.RunCommand(@"update dd.DXC set clqk = :clqk, clday = to_date(:clday,'yyyy-mm-dd hh24:mi:ss'), cbanzu = :cbanzu where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunCommand(@"delete from dd.DXC where num = :num", prams));
        }
        public string DXCS(string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.DXC where ku = :ku and clqk = '未处理'", prams);
            return obj.ToString();
        }
        public string JBDX(string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dun) from dd.DXC where ku = :ku and clqk = '未处理'", prams);
            return obj.ToString();
        }
        public DataSet Select(string bt, string et, string ku)
        {
            bt = bt + "00:00:00";
            et = et + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.DXC where ku = :ku and jday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "jbdxqk"));
        }
        public DataSet SelectKu(string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select num, ku, jbanzu, jday, neirong, cs, dun, clqk from dd.DXC where ku = :ku and clqk = '未处理'", prams, "jbdx"));
        }
        public string WCL(string ku, string neirong)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            DateTime dt4 = dt3.AddDays(-1);
            string jltime = "";
            if (DateTime.Compare(dt1, dt2) < 0)
            {
                jltime = dt4.ToString();
            }
            else if (DateTime.Compare(dt1, dt3) > 0)
            {
                jltime = dt3.ToString();
            }
            else
                jltime = dt2.ToString();
               
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong),
                DALHelper.MakeInParam(":jltime", OracleType.VarChar, jltime)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dun) from dd.DXC where ku = :ku and neirong = :neirong and clqk = '未处理' and jday > to_date(:jltime,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string YCL(string ku, string neirong)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            DateTime dt4 = dt3.AddDays(-1);
            string jltime = "";
            if (DateTime.Compare(dt1, dt2) < 0)
            {
                jltime = dt4.ToString();
            }
            else if (DateTime.Compare(dt1, dt3) > 0)
            {
                jltime = dt3.ToString();
            }
            else
                jltime = dt2.ToString();

            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong),
                DALHelper.MakeInParam(":jltime", OracleType.VarChar, jltime)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dun) from dd.DXC where ku = :ku and neirong = :neirong and clqk = '已处理' and clday > to_date(:jltime,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public DataSet SelectNum(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.DXC where num = :num", prams, "jt"));
        }
        public DataSet BanWCL(string bt, string et, string ku, string neirong, string jbanzu)
        {
            bt = bt + "20:00:00";
            et = et + "20:00:00";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong),
                DALHelper.MakeInParam(":jbanzu", OracleType.VarChar, jbanzu)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as wcs, round(sum(dun), 2) as wds from dd.DXC where jday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and ku = :ku and neirong = :neirong and jbanzu = :jbanzu", prams, "wcl"));
            
        }
        public DataSet BanYCL(string bt, string et, string ku, string neirong, string cbanzu)
        {
            bt = bt + "20:00:00";
            et = et + "20:00:00";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong),
                DALHelper.MakeInParam(":cbanzu", OracleType.VarChar, cbanzu)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as ycs, round(sum(dun), 2) as yds from dd.DXC where clday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and clday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and ku = :ku and neirong = :neirong and cbanzu = :cbanzu", prams, "ycl"));
            
        }
        public int UpdateXG(string num, string xg)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":xg", OracleType.VarChar, xg)
            };
            return (DALHelper.RunCommand(@"update dd.DXC set xgday = to_date(:xg,'yyyy-mm-dd hh24:mi:ss') where num = :num", prams));
        }
        public DataSet NRWCL(string t, string ku, string neirong)
        {
            string bt = Convert.ToDateTime(t).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, t),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as csw, sum(dun) as dunw from dd.DXC where ku = :ku and neirong = :neirong and clqk = '已处理' and jday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and clday >= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "yl"));
        }
        public DataSet NRYCL(string t, string ku, string neirong)
        {
            string bt = Convert.ToDateTime(t).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, t),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as csy, sum(dun) as duny from dd.DXC where ku = :ku and neirong = :neirong and clqk = '已处理' and clday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and clday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:bt,'yyyy-mm-dd hh24:mi:ss')", prams, "cl"));

        }
        public DataSet YNRYCL(string t, string neirong)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":t", OracleType.VarChar, t),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as csy, sum(dun) as duny from dd.DXC where neirong = :neirong and clday >= to_date(:t,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:t,'yyyy-mm-dd hh24:mi:ss')", prams, "cl"));

        }
        public DataSet JDYCL(string t, string neirong)
        {
            string et = Convert.ToDateTime(t).AddDays(1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, t),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as csy, sum(dun) as duny from dd.DXC where jday <= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and clday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and clday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and ku in ('嘉东线材', '嘉东螺纹', '嘉东卷板', '嘉东板材') and neirong = :neirong clqk = '已处理'", prams, "ycl"));

        }

        public DataSet JDWCL(string t, string neirong)
        {
            string et = Convert.ToDateTime(t).AddDays(1).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, t),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            return (DALHelper.RunProcReturn(@"select sum(cs) as csw, sum(dun) as dunw from dd.DXC where clday >= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and jday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and ku in ('嘉东线材', '嘉东螺纹', '嘉东卷板', '嘉东板材') and neirong = :neirong clqk = '已处理'", prams, "ycl"));

        }
        public string JDY(string bt, string et, string neirong)
        {
            
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.DXC where jday <= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and clday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and clday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and clqk = '已处理' and ku in ('嘉东线材', '嘉东螺纹', '嘉东卷板', '嘉东板材') and neirong = :neirong", prams);
            return obj.ToString();
        }
        public string JDW(string bt, string et, string neirong)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et),
                DALHelper.MakeInParam(":neirong", OracleType.VarChar, neirong)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(cs) from dd.DXC where clday >= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and jday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss') and clqk = '已处理' and ku in ('嘉东线材', '嘉东螺纹', '嘉东卷板', '嘉东板材') and neirong = :neirong", prams);
            return obj.ToString();
        }
    }
}
